import requests
import sqlite3
import json
import pandas as pd
import re


# 初始化数据库并创建表格
def initialize_db():
    connection = sqlite3.connect('university_data.db')
    db_cursor = connection.cursor()
    db_cursor.execute('''  
        CREATE TABLE IF NOT EXISTS university_info (  
            id INTEGER PRIMARY KEY AUTOINCREMENT,  
            学校 TEXT NOT NULL,  
            分数 REAL NOT NULL  
        )  
    ''')
    connection.commit()
    return connection, db_cursor


# 保存数据到数据库
def save_data(db_cursor, university_info):
    try:
        db_cursor.execute('''  
            INSERT INTO university_info (学校, 分数)  
            VALUES (?, ?)  
        ''', university_info)
    except sqlite3.IntegrityError:
        # 如果记录已存在（基于学校名称的唯一性），则忽略插入
        pass

    # 从API获取大学数据


def fetch_university_data(api_url):
    try:
        result = requests.get(api_url)
        result.raise_for_status()  # 如果状态码不是200，将引发HTTPError异常

        # 假设数据被包裹在某种JavaScript对象中，我们需要提取出JSON部分
        # 注意：这里的字符串操作是基于假设的API响应格式，实际使用时可能需要调整
        data_start = result.text.find('return {') + len('return {')
        data_end = result.text.find('};', data_start)  # 假设数据以'};'结尾
        data = result.text[data_start:data_end]

        # 使用正则表达式匹配所有大学数据
        pattern = re.compile(r'univNameCn:\s*"([^"]+)",\s*.*?score:\s*([\d\.]+)', re.DOTALL)
        matches = pattern.findall(data)

        # 将匹配结果转换为结构化数据
        universities = [{'university_name': match[0], 'score': float(match[1])} for match in matches]
        return universities
    except Exception as error:
        print(f"请求出错: {error}")
        return []

    # 显示并导出数据到Excel


def show_university_data(connection):
    query = "SELECT * FROM university_info"
    dataframe = pd.read_sql_query(query, connection)
    print(dataframe)
    dataframe.to_excel('university_data.xlsx', index=False)
    print("数据已导出到 university_data.xlsx 文件中。")


# 主逻辑：抓取并存储大学数据
def main_program():
    connection, db_cursor = initialize_db()
    api_url = "https://www.shanghairanking.cn/_nuxt/static/1728872418/rankings/bcur/2021/payload.js"

    university_list = fetch_university_data(api_url)
    for university in university_list:
        save_data(db_cursor, (university['university_name'], university['score']))

    connection.commit()
    connection.close()
    print("数据已成功存储在数据库中！")

    # 重新打开数据库连接以显示和导出数据（或者可以在save_data后保持连接打开）
    with sqlite3.connect('university_data.db') as connection:
        show_university_data(connection)

    # 运行程序


if __name__ == "__main__":
    main_program()